package com.db;
import com.db.DbConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
public class Show {
	public ArrayList showAllPath() {
		ArrayList<Map> paths=new ArrayList<Map>();
		String sql = "select * from all_path ";
		Connection con = null;
		
		
		try {
			con = DbConnection.getInstance().getConnection();
			Statement st = con.createStatement();
			Statement st1 = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			Map<String, Object> path = null;
			ResultSet temp=null;
			while (rs.next()) {
				path=new HashMap<String, Object>();
				path.put("pathname", rs.getString("pathname"));
				path.put("pathid", rs.getString("id"));
				System.out.println(rs.getString("pathname"));
				sql="select username from user where id="+rs.getString("userid");
				temp = st1.executeQuery(sql);
				temp.next();
				path.put("username", temp.getString("username"));
				System.out.println(temp.getString("username"));
				path.put("description", rs.getString("description"));
				//System.out.println(rs.getString("description"));
				paths.add(path);

			}
		} catch (SQLException e) {
			paths = null;
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return paths;
	}
	public ArrayList showMyPath(int userid) {
		ArrayList<Map> paths=new ArrayList<Map>();
		String sql = "select * from all_path where userid='"+userid+"'";
		Connection con = null;
		
		
		try {
			con = DbConnection.getInstance().getConnection();
			Statement st = con.createStatement();
			
			ResultSet rs = st.executeQuery(sql);
			Map<String, Object> path = null;
			ResultSet temp=null;
			while (rs.next()) {
				path=new HashMap<String, Object>();
				path.put("pathname", rs.getString("pathname"));
				System.out.println(rs.getString("pathname"));
				path.put("pathid", rs.getString("id"));
				path.put("description", rs.getString("description"));
				paths.add(path);

			}
		} catch (SQLException e) {
			paths = null;
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return paths;
	}
	public ArrayList showRoutesDetails(int pathid) {
		ArrayList<Map> paths=new ArrayList<Map>();
		String sql = "select * from route where pathid='"+pathid+"'"+" order by pathnum";
		Connection con = null;
		
		
		try {
			con = DbConnection.getInstance().getConnection();
			Statement st = con.createStatement();
			
			ResultSet rs = st.executeQuery(sql);
			Map<String, Object> path = null;
			ResultSet temp=null;
			while (rs.next()) {
				path=new HashMap<String, Object>();
				path.put("pathnum", rs.getString("pathnum"));
				
				path.put("slat", rs.getString("slatitude"));
				path.put("slng", rs.getString("slongitude"));
				path.put("elat", rs.getString("elatitude"));
				path.put("elng", rs.getString("elongitude"));
				paths.add(path);

			}
		} catch (SQLException e) {
			paths = null;
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return paths;
	}
	public ArrayList showMessenger(int pathid) {
		ArrayList<Map> msgs=new ArrayList<Map>();
		String sql = "select * from route_message where rid='"+pathid+"'";
		
		Connection con = null;
		
		
		try {
			con = DbConnection.getInstance().getConnection();
			Statement st = con.createStatement();
			Statement st1 = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			ResultSet rs1;
			Map<String, Object> msg = null;
			ResultSet temp=null;
			while (rs.next()) {
				String mid=rs.getString("mid");
				String rid=rs.getString("rid");
				String username="";
				msg=new HashMap<String, Object>();
				msg.put("msgid",mid );
				String sql1 = "select username from user where id='"+rid+"'";
				rs1=st1.executeQuery(sql1);
				if(rs1.next()){
					username = rs1.getString("username");
					msg.put("username",username );
				}
				sql1="select content from message where id='"+mid+"'";
				rs1=st1.executeQuery(sql1);
				if(rs1.next()){
					msg.put("message", username+":"+rs1.getString("content"));
				}
				msgs.add(msg);

			}
		} catch (SQLException e) {
			msgs = null;
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return msgs;
	}
}
